package com.neusoft.neuiotms.dcom.service.impl;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import javax.persistence.EntityManager;
import javax.persistence.EntityNotFoundException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.transaction.Transactional;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.neusoft.bizcore.auth.common.bean.UserBean;
import com.neusoft.bizcore.notice.constant.NoticeConsts;
import com.neusoft.bizcore.notice.notification.dto.NoticeDingDTO;
import com.neusoft.bizcore.notice.notification.dto.NoticeRecordDTO;
import com.neusoft.bizcore.notice.notification.dto.NoticeRecordSystemDTO;
import com.neusoft.bizcore.web.constant.ExceptionConstants;
import com.neusoft.bizcore.web.exception.UnitedException;
import com.neusoft.bizcore.web.support.Searchable;
import com.neusoft.bizcore.web.utils.EasyPoiExcelUtil;
import com.neusoft.bizcore.web.utils.EasyPoiStylerColorImpl;
import com.neusoft.bizcore.web.utils.JsonUtils;
import com.neusoft.neuiotms.common.dto.DictionaryDTO;
import com.neusoft.neuiotms.common.service.DictionaryService;
import com.neusoft.neuiotms.dcom.bean.ProjectUserBean;
import com.neusoft.neuiotms.dcom.constants.DCOMConstants;
import com.neusoft.neuiotms.dcom.converter.WorkDailyConverter;
import com.neusoft.neuiotms.dcom.dto.InstitutionsDTO;
import com.neusoft.neuiotms.dcom.dto.WorkDailyDTO;
import com.neusoft.neuiotms.dcom.model.Institutions;
import com.neusoft.neuiotms.dcom.model.OperationGroup;
import com.neusoft.neuiotms.dcom.model.WorkDaily;
import com.neusoft.neuiotms.dcom.model.enums.HnInspectionTaskStatus;
import com.neusoft.neuiotms.dcom.model.enums.InsuranceTaskStatus;
import com.neusoft.neuiotms.dcom.repository.InstitutionsRepository;
import com.neusoft.neuiotms.dcom.repository.OperationGroupRepository;
import com.neusoft.neuiotms.dcom.repository.WorkDailyRepository;
import com.neusoft.neuiotms.dcom.repository.hn.HnInspectionHealthTaskRepository;
import com.neusoft.neuiotms.dcom.repository.hn.HnInspectionTaskRepository;
import com.neusoft.neuiotms.dcom.repository.insurance.InsuranceInspectionTaskRepository;
import com.neusoft.neuiotms.dcom.service.InstitutionsService;
import com.neusoft.neuiotms.dcom.service.WorkDailyService;
import com.neusoft.neuiotms.dcom.util.PermUtils;
import com.neusoft.neuiotms.dcom.vo.WorkDailyExportVO;
import com.neusoft.neuiotms.dcom.vo.WorkDailyImportVO;
import com.neusoft.neuiotms.micro.notice.NoticeMicroService;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;

@Service
@Slf4j
public class WorkDailyServiceImpl implements WorkDailyService {

    @Autowired
    private WorkDailyRepository workDailyRepository;
    @Autowired
    private WorkDailyConverter workDailyConverter;
    @Autowired
    private DictionaryService dictionaryService;
    @Autowired
    private InstitutionsService institutionsService;
    @Autowired
    private OperationGroupRepository operationGroupRepository;
    @Autowired
    private InstitutionsRepository institutionsRepository;
    @PersistenceContext
    private EntityManager entityManager;
    @Autowired
    private NoticeMicroService noticeMicroService;
    @Autowired
    private HnInspectionTaskRepository hnInspectionTaskRepository;
    @Autowired
    private HnInspectionHealthTaskRepository hnInspectionHealthTaskRepository;
    @Autowired
    private InsuranceInspectionTaskRepository insuranceInspectionTaskRepository;

    @Override
    public List<WorkDailyDTO> index(final Searchable searchable, final UserBean userBean) {
        final List<InstitutionsDTO> institutions = this.institutionsService.getPermInstitutions(userBean);
        final List<String> institutionCodeArray =
                institutions.stream().map(it -> it.getCode()).collect(Collectors.toList());
        searchable.put("institutionCodeArray", institutionCodeArray);
        final List<WorkDaily> result = this.workDailyRepository.index(searchable);
        return this.workDailyConverter.toListDTO(result);
    }

    @Override
    public Page<WorkDailyDTO> search(final Pageable pageable, final Searchable searchable, final UserBean userBean) {
        final List<InstitutionsDTO> institutions = this.institutionsService.getPermInstitutions(userBean);
        Page<WorkDaily> result = null;
        if (institutions.size() > 0) {
            final List<String> institutionCodeArray =
                    institutions.stream().map(it -> it.getCode()).collect(Collectors.toList());
            searchable.put("institutionCodeArray", institutionCodeArray);
            if (PermUtils.isWorker(userBean)) {
                searchable.put("incidentUserCode", userBean.getUsername());
            }
            result = this.workDailyRepository.search(pageable, searchable);
        } else {
            result = new PageImpl<>(Lists.newArrayList(), pageable, 0);
        }

        return this.workDailyConverter.toPageDTO(result);
    }

    @Transactional
    @Override
    public WorkDailyDTO create(final WorkDailyDTO dto) {
        final WorkDaily model = this.workDailyConverter.toModel(dto);
        this.workDailyRepository.save(model);
        return this.workDailyConverter.toDTO(model);
    }

    @Transactional
    @Override
    public WorkDailyDTO update(final Long id, final WorkDailyDTO dto) {
        WorkDaily model;
        try {
            model = this.workDailyRepository.getOne(id);
        } catch (final EntityNotFoundException e) {
            throw new UnitedException(ExceptionConstants.ENTITY_NOT_FOUND, new Object[] {id });
        }
        dto.setId(model.getId());
        model = this.workDailyConverter.toModel(dto);
        this.workDailyRepository.save(model);
        return this.workDailyConverter.toDTO(model);
    }

    @Override
    public WorkDailyDTO details(final Long id) {
        final WorkDaily model;
        try {
            model = this.workDailyRepository.getOne(id);
        } catch (final EntityNotFoundException e) {
            throw new UnitedException(ExceptionConstants.ENTITY_NOT_FOUND, new Object[] {id });
        }
        return this.workDailyConverter.toDTO(model);
    }

    @Transactional
    @Override
    public void delete(final Long id) {
        try {
            this.workDailyRepository.deleteById(id);
        } catch (final EntityNotFoundException e) {
            WorkDailyServiceImpl.log.error(e.getMessage(), e);
            throw new UnitedException(e.getMessage(), new Object[] {id });
        }
    }

    @Override
    public Workbook export(final Searchable searchable, final UserBean userBean) {

        final List<DictionaryDTO> source = this.dictionaryService.findByDictionaryType(DCOMConstants.HN_EVENT_GRADE);
        final Map<String, String> sourceMap =
                source.stream().collect(Collectors.toMap(DictionaryDTO::getCode, DictionaryDTO::getText));

        final List<InstitutionsDTO> institutions = this.institutionsService.getPermInstitutions(userBean);
        final List<String> institutionCodeArray =
                institutions.stream().map(it -> it.getCode()).collect(Collectors.toList());
        searchable.put("institutionCodeArray", institutionCodeArray);

        final List<WorkDaily> models = this.workDailyRepository.index(searchable);
        final List<WorkDailyExportVO> list = new ArrayList<>(models.size() == 0 ? 1 : models.size());
        models.stream().map(t -> {
            final WorkDailyExportVO vo = this.workDailyConverter.toVO(t);
            if (!StringUtils.isEmpty(vo.getEventLevel())) {
                vo.setEventLevel(sourceMap.get(vo.getEventLevel()));
            }
            return vo;
        }).forEach(list::add);

        final ExportParams param =
                new ExportParams(DCOMConstants.WORK_DAILY_TITLE, DCOMConstants.WORK_DAILY_NAME, ExcelType.XSSF);
        param.setStyle(EasyPoiStylerColorImpl.class);
        return ExcelExportUtil.exportExcel(param, WorkDailyExportVO.class, list);
    }

    //    @Override
    //    public List<InstitutionsDTO> unstartdInstitutions() {
    //        final List<InstitutionsDTO> institutions = this.institutionsService.index(new Searchable());
    //        final Calendar c = Calendar.getInstance();
    //        c.set(Calendar.HOUR_OF_DAY, 0);
    //        c.set(Calendar.MINUTE, 0);
    //        c.set(Calendar.SECOND, 0);
    //        c.set(Calendar.MILLISECOND, 0);
    //        final Date start = c.getTime();
    //
    //        c.set(Calendar.HOUR_OF_DAY, 23);
    //        c.set(Calendar.MINUTE, 59);
    //        c.set(Calendar.SECOND, 59);
    //        c.set(Calendar.MILLISECOND, 0);
    //        final Date end = c.getTime();
    //        final List<WorkDaily> workDailies = this.workDailyRepository.findAllByDailyReportDateBetween(start, end);
    //        return institutions.stream()
    //                .filter(it -> workDailies.stream().filter(w -> it.getCode().equals(w.getUnitCode())).count() == 0)
    //                .collect(Collectors.toList());
    //
    //    }

    @Override
    public Map<String, Long> reportStats(final Date date) {
        final List<InstitutionsDTO> institutions = this.institutionsService.index(new Searchable());

        final Calendar c = Calendar.getInstance();
        c.setTime(date);
        c.set(Calendar.HOUR_OF_DAY, 0);
        c.set(Calendar.MINUTE, 0);
        c.set(Calendar.SECOND, 0);
        c.set(Calendar.MILLISECOND, 0);
        final Date start = c.getTime();

        c.set(Calendar.HOUR_OF_DAY, 23);
        c.set(Calendar.MINUTE, 59);
        c.set(Calendar.SECOND, 59);
        c.set(Calendar.MILLISECOND, 0);
        final Date end = c.getTime();
        final List<WorkDaily> workDailies = this.workDailyRepository.findAllByDailyReportDateBetween(start, end);

        final Map<String, Long> result = Maps.newHashMap();
        final long reportedCount = workDailies.stream().map(it -> it.getUnitCode()).distinct().count();
        result.put("已提交", reportedCount);
        result.put("未提交", institutions.size() - reportedCount);

        return result;
    }

    @Override
    @Transactional
    public void importExcel(final InputStream fileStream, final UserBean user) {
        final StringBuilder errorMsg = new StringBuilder();
        final ImportParams params = new ImportParams();
        params.setHeadRows(1);
        params.setNeedVerify(true);
        final List<DictionaryDTO> source = this.dictionaryService.findByDictionaryType(DCOMConstants.SERVICE_TYPE);
        final Map<String, String> sourceMap =
                source.stream().collect(Collectors.toMap(DictionaryDTO::getCode, DictionaryDTO::getText));
        final Map<String, List<String>> allAttributes = new HashMap<>();
        for (final Map.Entry<String, String> entry : sourceMap.entrySet()) {
            final List<String> attributes = new ArrayList<>();
            final List<DictionaryDTO> dtos =
                    this.dictionaryService.findByDictionaryType(entry.getKey());
            dtos.stream().map(t -> {
                return t.getCode();
            }).forEach(attributes::add);
            allAttributes.put(entry.getKey(), attributes);
        }

        try {
            final ExcelImportResult<WorkDailyImportVO> result =
                    ExcelImportUtil.importExcelMore(fileStream, WorkDailyImportVO.class, params);
            if (result.getList().isEmpty() && result.getFailList().isEmpty()) {
                throw new UnitedException("日报上传数据为空");
            }
            if (result.isVerifyFail()) {
                final List<WorkDailyImportVO> failList = result.getFailList();
                failList.forEach(l -> {
                    errorMsg.append("第" + (l.getRowNum() + 1) + "行," + l.getErrorMsg() + "! \n");
                });
            }
            if (errorMsg.length() > 0) {
                WorkDailyServiceImpl.log.error(errorMsg.toString());
                throw new UnitedException(errorMsg.toString(), new Object[] {});
            }

            final List<WorkDailyImportVO> saveList = result.getList();
            final String currentUser = user.getName() + DCOMConstants.DICTIONARY_SPLITOR + user.getUsername();
            final List<WorkDailyImportVO> person = saveList.stream().filter(t -> {
                return !t.getIncidentUser().equals(currentUser);
            }).collect(Collectors.toList());
            if (person.size() > 0) {
                throw new UnitedException("导入日报事件处理人必须为当前登录用户：" + user.getName());
            }
            final List<WorkDaily> models = new ArrayList<>();
            for (int i = 0; i < saveList.size(); i++) {
                final WorkDailyImportVO portVO = saveList.get(i);
                final WorkDaily model = this.workDailyConverter.toModel(portVO);
                if (StringUtils.isNotBlank(model.getServiceTypeCode())
                        && StringUtils.isNotBlank(model.getServiceAttributesCode())) {
                    if (!allAttributes.containsKey(model.getServiceTypeCode())
                            || CollectionUtils.isEmpty(allAttributes.get(model.getServiceTypeCode())) || !allAttributes
                                    .get(model.getServiceTypeCode()).contains(model.getServiceAttributesCode())) {
                        throw new UnitedException((i + 1) + "行，服务类型和服务属性填写错误");
                    }
                    if (model.getServiceEndTime().getTime() > new Date().getTime()) {
                        throw new UnitedException((i + 1) + "行，任务完成时间不应该大于当前时间");
                    }
                    models.add(model);
                }
            }
            if (!models.isEmpty() && (errorMsg.length() == 0)) {
                this.workDailyRepository.saveAll(models);
            }

        } catch (final Exception e) {
            e.printStackTrace();
            WorkDailyServiceImpl.log.error("上传失败", e);
            throw new UnitedException(e.getMessage().toString());
        } finally {
            try {
                fileStream.close();
            } catch (final IOException e) {
                e.printStackTrace();
                throw new UnitedException(e.getMessage().toString());
            }
        }

    }

    @Override
    public Workbook template(final UserBean user) {
        final List<WorkDailyImportVO> list = new ArrayList<>();
        final Workbook workbook = ExcelExportUtil.exportExcel(
                new ExportParams(null, DCOMConstants.WORK_DAILY_TEMPLATE_NAME, ExcelType.XSSF), WorkDailyImportVO.class,
                list);

        //服务类型下拉候选框
        final List<String> serviceTypes = new ArrayList<>();
        final List<DictionaryDTO> type =
                this.dictionaryService.findByDictionaryType(DCOMConstants.SERVICE_TYPE);
        type.stream().map(t -> {
            return t.getText() + DCOMConstants.DICTIONARY_SPLITOR + t.getCode();
        }).forEach(serviceTypes::add);
        final Map<String, String> sourceMap =
                type.stream().collect(Collectors.toMap(DictionaryDTO::getCode, DictionaryDTO::getText));
        //                        break;
        //服务属性候选框数据
        final Map<String, List<String>> children = new HashMap<>();
        for (final Map.Entry<String, String> entry : sourceMap.entrySet()) {
            final List<String> attributes = new ArrayList<>();
            final List<DictionaryDTO> dtos =
                    this.dictionaryService.findByDictionaryType(entry.getKey());
            dtos.stream().map(t -> {
                return t.getText() + DCOMConstants.DICTIONARY_SPLITOR + t.getCode();
            }).forEach(attributes::add);
            children.put(entry.getValue() + DCOMConstants.DICTIONARY_SPLITOR + entry.getKey(), attributes);
        }

        final ArrayList<Integer> columns = Lists.newArrayList(DCOMConstants.WORK_DAILY_SERVICE_TYPE_NO,
                DCOMConstants.WORK_DAILY_SERVICE_ATTRIBUTES_NO);
        //级联
        EasyPoiExcelUtil.cascade(workbook, serviceTypes, serviceTypes,
                children,
                columns, 0, DCOMConstants.ASSET_START_CONSTRAINT_ROW_FIRST, DCOMConstants.ASSET_CONSTRAINT_ROW_65535);
        //下拉框候选数据
        final List<Map<String, Object>> constraint = this.getConstraint(user);
        EasyPoiExcelUtil.setConstraint(constraint, workbook, DCOMConstants.ASSET_START_CONSTRAINT_ROW_FIRST,
                DCOMConstants.ASSET_CONSTRAINT_ROW_65535);
        return workbook;
    }

    //位置下拉候选框数据
    private List<Map<String, Object>> getConstraint(final UserBean userBean) {
        final List<Map<String, Object>> constraint = new ArrayList<>();
        for (final Integer element : DCOMConstants.WORK_DAILY_CONSTRAINT) {
            final List<String> constraintList = new ArrayList<>();
            final Integer col = element;
            switch (element) {
            //单位信息
            case DCOMConstants.WORK_DAILY_UNIT_NO:
                final List<InstitutionsDTO> institutions = this.institutionsService.getPermInstitutions(userBean);
                institutions.stream().map(t -> {
                    return t.getName() + DCOMConstants.DICTIONARY_SPLITOR + t.getCode();
                }).forEach(constraintList::add);
                break;
            //运维组
            case DCOMConstants.WORK_DAILY_GROUP_NO:
                OperationGroup opeation =
                        this.operationGroupRepository.findByGroupLeaderCode(userBean.getUsername());
                if (null == opeation) {
                    final Institutions institutionModels =
                            this.institutionsRepository
                                    .findByResidents_UsernameIn(Arrays.asList(userBean.getUsername()));
                    opeation =
                            this.operationGroupRepository.findByInstitutionsIn(Lists.newArrayList(institutionModels));
                }
                final String group = opeation.getName() + DCOMConstants.DICTIONARY_SPLITOR + opeation.getCode();
                constraintList.add(group);
                break;
            //事件处理人拉候选框数据
            case DCOMConstants.WORK_DAILY_OPERATOR_NO:
                final ProjectUserBean projectInfo = this.institutionsService.getMyInstitutions(userBean);
                projectInfo.getInstitutions().forEach(inst -> {
                    inst.getResidents().forEach(r -> {
                        constraintList.add(r.getName() + DCOMConstants.DICTIONARY_SPLITOR + r.getUsername());
                    });
                    inst.getUsers().forEach(r -> {
                        constraintList.add(r.getName() + DCOMConstants.DICTIONARY_SPLITOR + r.getUsername());
                    });
                });
                break;
            //事件级别候选
            case DCOMConstants.WORK_DAILY_EVENT_LEVEL_NO:
                final List<DictionaryDTO> source =
                        this.dictionaryService.findByDictionaryType(DCOMConstants.HN_EVENT_GRADE);
                source.stream().map(t -> {
                    return t.getText() + DCOMConstants.DICTIONARY_SPLITOR + t.getCode();
                }).forEach(constraintList::add);
                break;
            }
            if (!constraintList.isEmpty()) {
                final Map<String, Object> map = new HashMap<>();
                map.put("list", constraintList);
                map.put("col", col);
                constraint.add(map);
            }
        }
        return constraint;
    }

    @SuppressWarnings("unchecked")
    @Override
    public Page<Map<String, Object>> report(Pageable pageable, final String username, final Date start,
            final Date end) {

        final String countSql = this.buildTotalSql(username, start, end);
        WorkDailyServiceImpl.log.info(countSql);
        final Query query1 = this.entityManager.createNativeQuery(countSql);
        query1.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        final List<Map<String, Object>> result1 = query1.getResultList();
        int total = 0;
        if (result1.size() > 0) {
            total = Integer.parseInt(String.valueOf(result1.get(0).get("count")));
        }

        final String sql = this.buildQuerySql(pageable, username, start, end);
        WorkDailyServiceImpl.log.info(sql);
        final Query query = this.entityManager.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        final List<Map<String, Object>> result = query.getResultList();
        return new PageImpl<>(result, pageable, total);
    }

    @SuppressWarnings("unchecked")
    @Override
    public Workbook exportReport(final String username, final Date start, final Date end) {
        final String sql = this.buildExportSql(username, start, end);
        WorkDailyServiceImpl.log.info(sql);
        final Query query = this.entityManager.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        final List<Map<String, Object>> result = query.getResultList();

        final List<DailyReportVO> list = new ArrayList<>(result.size() == 0 ? 1 : result.size());
        result.stream().map(t -> {
            final DailyReportVO vo = new DailyReportVO();
            vo.setIncident_user_name(t.get("incident_user_name"));
            vo.setDays(t.get("days"));
            vo.setTotal_working_hours(t.get("total_working_hours"));
            vo.setTotal_inspect_hours(t.get("total_inspect_hours"));
            vo.setTotal_malfunction_hours(t.get("total_malfunction_hours"));
            vo.setTotal_other_hours(t.get("total_other_hours"));
            return vo;
        }).forEach(list::add);

        final ExportParams param =
                new ExportParams("日报统计", "日报统计", ExcelType.XSSF);
        param.setStyle(EasyPoiStylerColorImpl.class);
        return ExcelExportUtil.exportExcel(param, DailyReportVO.class, list);
    }

    private String buildTotalSql(final String username, final Date start, final Date end) {
        final String statement = " count(*) as count ";
        return this.buildSql(username, start, end, statement);
    }

    private String buildQuerySql(Pageable pageable, final String username, final Date start, final Date end) {
        final String statement = " t1.incident_user_name, "
                + " t2.days, "
                + " CONVERT ( "
                + "     t1.total_working_minutes / 60, "
                + "     DECIMAL (10, 1) "
                + " ) AS total_working_hours, "
                + " CONVERT ( "
                + "     t3.inspect_minutes / 60, "
                + "     DECIMAL (10, 1) "
                + " ) AS total_inspect_hours, "
                + " CONVERT ( "
                + "     t3.malfunction_minutes / 60, "
                + "     DECIMAL (10, 1) "
                + " ) AS total_malfunction_hours, "
                + " CONVERT ( "
                + "     ( "
                + "         t1.total_working_minutes - t3.inspect_minutes - t3.malfunction_minutes "
                + "     ) / 60, "
                + "     DECIMAL (10, 1) "
                + " ) AS total_other_hours ";
        String noPageSql = this.buildSql(username, start, end, statement);
        noPageSql += " limit " + pageable.getPageSize() + " offset " + pageable.getOffset();

        return noPageSql;
    }

    private String buildExportSql(final String username, final Date start, final Date end) {
        final String statement = " t1.incident_user_name, "
                + " t2.days, "
                + " CONVERT ( "
                + "     t1.total_working_minutes / 60, "
                + "     DECIMAL (10, 1) "
                + " ) AS total_working_hours, "
                + " CONVERT ( "
                + "     t3.inspect_minutes / 60, "
                + "     DECIMAL (10, 1) "
                + " ) AS total_inspect_hours, "
                + " CONVERT ( "
                + "     t3.malfunction_minutes / 60, "
                + "     DECIMAL (10, 1) "
                + " ) AS total_malfunction_hours, "
                + " CONVERT ( "
                + "     ( "
                + "         t1.total_working_minutes - t3.inspect_minutes - t3.malfunction_minutes "
                + "     ) / 60, "
                + "     DECIMAL (10, 1) "
                + " ) AS total_other_hours ";
        return this.buildSql(username, start, end, statement);
    }

    private String buildSql(final String username, final Date start, final Date end, String statement) {
        String where = "where 1=1 ";
        if (StringUtils.isNotBlank(username)) {
            where += "and incident_user_code='" + username + "' ";
        }
        if (null != start) {
            where += "and daily_report_date>='" + DateFormatUtils.format(start, "yyyy-MM-dd") + "' ";
        }
        if (null != end) {
            final Calendar c = Calendar.getInstance();
            c.setTime(end);
            c.add(Calendar.DAY_OF_MONTH, 1);
            where += "and daily_report_date<'" + DateFormatUtils.format(c.getTime(), "yyyy-MM-dd") + "' ";
        }
        String sql = "SELECT "
                + statement
                + " FROM "
                + " ( "
                + "     SELECT "
                + "         incident_user_code, "
                + "         incident_user_name, "
                + "         sum(total_working_hours) AS total_working_minutes "
                + "     FROM "
                + "         ( "
                + "             SELECT "
                + "                 *, DATE(daily_report_date) AS report_date "
                + "             FROM "
                + "                 neuiot_work_daily "
                + "             %s "
                + "         ) t1 "
                + "     %s "
                + "     GROUP BY "
                + "         incident_user_code, "
                + "         incident_user_name "
                + " ) t1 "
                + "LEFT JOIN ( "
                + " SELECT "
                + "     incident_user_code, "
                + "     count(report_date) AS days "
                + " FROM "
                + "     ( "
                + "         SELECT "
                + "             incident_user_code, DATE(daily_report_date) AS report_date "
                + "         FROM "
                + "             neuiot_work_daily "
                + "         %s "
                + "         GROUP BY "
                + "           incident_user_code, "
                + "           report_date "
                + "     ) t1 "
                // + " %s "
                + " GROUP BY "
                + "     incident_user_code "
                + ") t2 ON t1.incident_user_code = t2.incident_user_code "
                + "LEFT JOIN ( "
                + " SELECT "
                + "     incident_user_code, "
                + "     incident_user_name, "
                + "     max( "
                + "         CASE service_attributes_name "
                + "         WHEN '巡检' THEN "
                + "             total_working_minutes "
                + "         ELSE "
                + "             0 "
                + "         END "
                + "     ) 'inspect_minutes', "
                + "     max( "
                + "         CASE service_attributes_name "
                + "         WHEN '故障处理' THEN "
                + "             total_working_minutes "
                + "         ELSE "
                + "             0 "
                + "         END "
                + "     ) 'malfunction_minutes' "
                + " FROM "
                + "     ( "
                + "         SELECT "
                + "             incident_user_code, "
                + "             incident_user_name, "
                + "             service_attributes_name, "
                + "             sum(total_working_hours) AS total_working_minutes "
                + "         FROM "
                + "             neuiot_work_daily "
                + "         %s "
                + "         GROUP BY "
                + "             incident_user_code, "
                + "             incident_user_name, "
                + "             service_attributes_name "
                + "     ) t "
                + " GROUP BY "
                + "     incident_user_code, "
                + "     incident_user_name "
                + ") t3 ON t1.incident_user_code = t3.incident_user_code ";

        sql = sql.replace("%s", where);

        return sql;
    }

    @Setter
    @Getter
    static class DailyReportVO {

        @Excel(name = "人员", orderNum = "0")
        private Object incident_user_name;

        @Excel(name = "填报天数", orderNum = "1")
        private Object days;

        @Excel(name = "服务时长（小时）", orderNum = "2", width = 30)
        private Object total_working_hours;

        @Excel(name = "巡检任务（小时）", orderNum = "3", width = 30)
        private Object total_inspect_hours;

        @Excel(name = "故障处理（小时）", orderNum = "4", width = 30)
        private Object total_malfunction_hours;

        @Excel(name = "其它相关（小时）", orderNum = "4", width = 30)
        private Object total_other_hours;

    }

    @Override
    public long serviceTimeTotal(final UserBean user) {
        final ProjectUserBean projectInfo = this.institutionsService.getMyInstitutions(user);

        if (user.getRoles().stream().filter(it -> DCOMConstants.ROLE_WORKER.equals(it.getRole())).count() > 0) {
            final Searchable searchable = new Searchable();
            searchable.put("incidentUserName", user.getName());
            final long total = this.workDailyRepository.totalServiceTime(searchable);
            final Double hours = (total / 60.0);
            return hours.longValue();
        } else if (user.getRoles().stream().filter(it -> DCOMConstants.ROLE_WORKER_GROUP_LEADER.equals(it.getRole()))
                .count() > 0) {
            final Searchable searchable = new Searchable();
            searchable.put("institutionCodeArray",
                    projectInfo.getInstitutions().stream().map(it -> it.getCode()).collect(Collectors.toList()));
            final long total = this.workDailyRepository.totalServiceTime(searchable);
            final Double hours = (total / 60.0);
            return hours.longValue();
        } else {
            final Searchable searchable = new Searchable();
            final long total = this.workDailyRepository.totalServiceTime(searchable);
            final Double hours = (total / 60.0);
            return hours.longValue();
        }
    }

    @Override
    public void checkDailyReportNotice() {

        final List<String> users = this.notWriteDailyReportResidents();
        if (users.size() == 0) {
            return;
        }

        final NoticeRecordDTO notice = new NoticeRecordDTO();
        notice.setCreateTime(new Date());
        notice.setAppCode("dcom");
        notice.setType(Lists.newArrayList(NoticeConsts.INFORM_TYPE_NOTICE, NoticeConsts.INFORM_TYPE_DINGDING));
        notice.setTitle("工作日志提醒");

        final NoticeRecordSystemDTO toSystem = new NoticeRecordSystemDTO();
        toSystem.setNoticeType("日志提醒");
        toSystem.setNoticeRoute("hn_workDaily");
        toSystem.setUsers(users);
        toSystem.setContent("请在17时前填写工作日志");
        notice.setSystem(toSystem);

        final NoticeDingDTO toDing = new NoticeDingDTO();
        toDing.setMsgtype("link");
        toDing.setUsers(users);
        final Map<String, String> linkParams = Maps.newHashMap();
        linkParams.put("messageUrl", "eapp://pages/daily/daily");
        linkParams.put("picUrl", "@lALPDeRETJL6S3XMtMy0");
        linkParams.put("title", "日志提醒");
        linkParams.put("text", "请在17时前填写工作日志");
        toDing.setJsonBody(JsonUtils.pojoToJson(linkParams));
        notice.setDing(toDing);
        this.noticeMicroService.createNotification(JsonUtils.pojoToJson(notice));

    }

    private List<String> notWriteDailyReportResidents() {
        // 1. 查找所有的厅局
        final List<InstitutionsDTO> institutions = this.institutionsService.index(new Searchable());

        // 2. 找到所有厅局的所有驻场工程师
        final List<String> residents = Lists.newArrayList();
        institutions.forEach(inst -> {
            final List<String> residentsOfInst =
                    inst.getResidents().stream().map(r -> r.getUsername()).collect(Collectors.toList());
            residents.addAll(residentsOfInst);
        });

        // 3. 查询所有一天内所有的日报
        final Calendar start = DateUtils.truncate(Calendar.getInstance(), Calendar.DAY_OF_MONTH);
        final Calendar end = DateUtils.ceiling(Calendar.getInstance(), Calendar.DAY_OF_MONTH);
        final List<WorkDaily> workDailies =
                this.workDailyRepository.findAllByDailyReportDateBetween(start.getTime(), end.getTime());

        // 4. 找出未填当日日报的人
        return residents.stream()
                .filter(it -> workDailies.stream().filter(w -> it.equals(w.getIncidentUserCode())).count() == 0)
                .collect(Collectors.toList());

    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Map<String, Object>> getServiceSummaryOfWorker(int year, String questionnaireType, UserBean user) {
        final Calendar cal = Calendar.getInstance();
        cal.set(year, 0, 1, 0, 0, 0);
        final String from = DateFormatUtils.format(cal, "yyyy-MM-dd HH:mm:ss");
        // 年中总结
        if ("MIDDLE_YEAR_SUMMARY".equals(questionnaireType)) {
            cal.set(year, 5, 30, 0, 0, 0);
        } else {
            cal.set(year, 11, 31, 0, 0, 0);
        }
        final String to = DateFormatUtils.format(cal, "yyyy-MM-dd HH:mm:ss");

        final String sql = "SELECT "
                + " service_type_code, "
                + " count(*) AS times "
                + "FROM "
                + " neuiot_work_daily "
                + "WHERE "
                + " service_type_code <> '' "
                + "AND daily_report_date BETWEEN '" + from + "' "
                + "AND '" + to + "' "
                + "AND incident_user_code = '" + user.getUsername() + "' "
                + "GROUP BY "
                + " service_type_code ";
        final Query query = this.entityManager.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        final List<Map<String, Object>> result = query.getResultList();

        final int statsRoutineWorkCount = this.hnInspectionTaskRepository.countByUserCodeAndStatusIn(user.getUsername(),
                Lists.newArrayList(HnInspectionTaskStatus.finished));
        final int statsRoutineWorkCount1 =
                this.hnInspectionHealthTaskRepository.countByUserCodeAndStatusIn(user.getUsername(),
                        Lists.newArrayList(HnInspectionTaskStatus.finished));
        final Map<String, Object> m1 = new HashMap<>();
        m1.put("service_type_code", "statsRoutineWork");
        m1.put("times", statsRoutineWorkCount + statsRoutineWorkCount1);
        result.add(m1);

        final int statsInsuranceWorkCount = this.insuranceInspectionTaskRepository
                .countByExecutorCodeAndStatusIn(user.getUsername(), Lists.newArrayList(InsuranceTaskStatus.completed));
        final Map<String, Object> m2 = new HashMap<>();
        m2.put("service_type_code", "statsInsuranceWork");
        m2.put("times", statsInsuranceWorkCount);
        result.add(m2);

        return result;
    }
}
